﻿using BCCommon;
using BCCommon.TMS.Driver;
using BCEntity.TMS.WorkingReport;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.WorkingReport
{
    public class WorkingReportData : IWorkingReportData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT WorkingReportId,TodayFinishedContent,UnfinishedContent,NeedCoordinatedContent,CreateTime,Role,CompanyId,DriverId,Phone,Name,Remark,Attachment,ProvinceId,ProvinceCode,ProvinceName,CityId,CityCode,CityName,CountyId,CountyCode,CountyName FROM WorkingReport";

        public WorkingReportData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<WorkingReportEntity> GetAsync(long workingReportId)
        {
            string sql = string.Format("{0} WHERE  WorkingReportId=?WorkingReportId;", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkingReportId", workingReportId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<WorkingReportEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<WorkingReportEntity>, int)> GetListAsync(long? companyId, string phone, DateTime? start, DateTime? end, DriverRoleType? role, bool? ascending, string sortName, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (companyId.HasValue)
            {
                wheres.Add("CompanyId =?CompanyId");
                parameters.Add("?CompanyId", companyId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add("Phone LIKE CONCAT('%',CONCAT(?Phone,'%'))");
                parameters.Add("?Phone", phone.Trim(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            if (role.HasValue)
            {
                wheres.Add("Role =?Role");
                parameters.Add("?Role", role.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format("{0} {1} ORDER BY {4} {5} LIMIT {2}, {3}; SELECT COUNT(1) FROM WorkingReport{1};",
              QUERY,
               wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
              (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
              pageSize,
              string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
              ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await databaseContext.Slave.QueryMultipleAsync<WorkingReportEntity, int>(sql, parameters);
        }

        public async Task<WorkingReportEntity> InsertAsync(WorkingReportEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("WorkingReport", new[] { "UnfinishedContent", "NeedCoordinatedContent", "Role", "CompanyId", "DriverId", "Phone", "Name", "Remark", "Attachment", "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId", "CountyCode", "CountyName" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UnfinishedContent", entity.UnfinishedContent, DbType.AnsiString, size: 1000);
            parameters.Add("?NeedCoordinatedContent", entity.NeedCoordinatedContent, DbType.AnsiString, size: 1000);
            parameters.Add("?Role", entity.Role, DbType.AnsiString, size: 20);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int64);
            parameters.Add("?DriverId", entity.DriverId, DbType.Int64);
            parameters.Add("?Phone", entity.Phone, DbType.AnsiString);
            parameters.Add("?Name", entity.Name, DbType.AnsiString);
            parameters.Add("?Remark", entity.Remark, DbType.AnsiString);
            parameters.Add("?Attachment", entity.Attachment, DbType.AnsiString);
            parameters.Add("?ProvinceId", entity.ProvinceId, DbType.Int64);
            parameters.Add("?ProvinceCode", entity.ProvinceCode, DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", entity.ProvinceName, DbType.AnsiString, size: 50);
            parameters.Add("?CityId", entity.CityId, DbType.Int64);
            parameters.Add("?CityCode", entity.CityCode, DbType.AnsiString, size: 20);
            parameters.Add("?CityName", entity.CityName, DbType.AnsiString, size: 50);
            parameters.Add("?CountyId", entity.CountyId, DbType.Int64);
            parameters.Add("?CountyCode", entity.CountyCode, DbType.AnsiString, size:20);
            parameters.Add("?CountyName", entity.CountyName, DbType.AnsiString,size:100);
            sql = string.Format("{0} {1} WHERE WorkingReportId = LAST_INSERT_ID()", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<WorkingReportEntity>(sql, parameters);
        }
    }
}
